package com.wd.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.apache.log4j.helpers.LogLog;

import com.wd.util.DbWatch;
import com.wd.util.ResPool;

/**
 * @author long
 * @Create 2012-10-8 上午9:54:25
 * @QQ 108232706
 * @version 1.0
 * @description BaseDao
 */
public abstract class BaseDao {
	public final DBMgr dbMgr;
	public boolean showSql = false;

	public BaseDao(DBMgr _dbMgr) {
		this.dbMgr = _dbMgr;
	}

	protected int execNoneQuery(String sql) {
		return execNoneQuery(sql, null);
	}

	protected int execNoneQuery2(String sql, int dbType) {
		return execNoneQuery2(sql, dbType, null);
	}

	protected int execNoneQuery(String sql, Map<Integer, DbParameter> params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		int result = -1;
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return result;
		}
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			prepareCommand(pstmt, params);
			result = pstmt.executeUpdate();
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + sql + "出错" });
			LogLog.error(msg, ex);
		} finally {
			closeConn(conn, pstmt);
			watch.commit(sql);
		}
		return result;
	}

	protected int execNoneQuery2(String sql, int dbType, Object... params) {
		/*if (showSql) {
			StringBuilder sb = new StringBuilder(sql);
			for (Object object : params) {
				sb.append(object + ",");
			}
			LogLog.debug(sb.toString());
		}*/
		int result = -1;
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		this.dbMgr.lock.lock();
		this.dbMgr.setCurrentDb(dbType);
		try {

			Connection conn = openConn();
			watch.getPool();
			if (conn == null) {
				watch.commit(sql);
				return result;
			}
			PreparedStatement pstmt = null;
			try {
				pstmt = conn.prepareStatement(sql);
				prepareCommand2(pstmt, params);
				result = pstmt.executeUpdate();
			} catch (Exception ex) {
				ex.printStackTrace();
				LogLog.error("[DataBase][database:" + dbMgr.getCurrentDbName() + " dbindex:" + dbMgr.index + "] 提示消息:当前Sql语句出错; 错误描述:%" + ex.getMessage() + "; 当前状态:" + "调用Sql语句" + sql + "出错", ex);
			} finally {
				closeConn(conn, pstmt);
				watch.commit(sql);
			}
		} catch (Exception e) {
		} finally {
			this.dbMgr.lock.unlock();
		}
		return result;
	}

	protected PreparedStatement execQuery(String sql) {
		return execQuery(sql, null);
	}

	protected PreparedStatement execQuery2(String sql) {
		return execQuery2(sql, null);
	}

	protected PreparedStatement execQuery(String sql, Map<Integer, DbParameter> params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return null;
		}
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			prepareCommand(pstmt, params);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + sql + "出错" });
			LogLog.error(msg, ex);
			closeConn(conn, pstmt);
		} finally {
			watch.commit(sql);
		}
		return pstmt;
	}

	protected PreparedStatement execQuery2(String sql, Object... params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return null;
		}
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			prepareCommand2(pstmt, params);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + sql + "出错" });
			LogLog.error(msg, ex);
			closeConn(conn, pstmt);
		} finally {
			watch.commit(sql);
		}
		return pstmt;
	}

	protected Statement getStatement() {
		Connection conn = openConn();
		Statement stmt = null;
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (Exception ex) {
			LogLog.error("[DataBase] getStatement错误", ex);
			closeConn(conn, stmt);
		} finally {

		}
		return stmt;
	}

	protected int[] sqlBatch(String sql, int batchCount, Object... params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return null;
		}
		PreparedStatement pstmt = null;
		int[] result = null;
		try {
			conn.setAutoCommit(false);
			// 预编译SQL语句,只编译一回,效率高啊
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < batchCount; i++) {
				prepareAddBatch(pstmt, params);
			}
			result = pstmt.executeBatch();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + sql + "出错" });
			LogLog.error(msg, ex);
			closeConn(conn, pstmt);
		} finally {
			watch.commit(sql);
		}
		return result;
	}

	protected CallableStatement execProduce(String produceName, Map<Integer, DbParameter> params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(produceName);
			return null;
		}
		CallableStatement stmt = null;
		try {
			stmt = conn.prepareCall(ProcedureName(produceName, params.size()), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			prepareCallable(stmt, params);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + produceName + "出错" });
			LogLog.error(msg, ex);
		} finally {
			closeConn(conn, stmt);
			watch.commit(produceName);
		}
		return stmt;
	}

	protected CallableStatement execProduce2(String produceName, Object... params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(produceName);
			return null;
		}
		CallableStatement stmt = null;
		try {
			stmt = conn.prepareCall(ProcedureName(produceName, params.length), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			prepareCallable2(stmt, params);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + produceName + "出错" });
			LogLog.error(msg + conn, ex);
			closeConn(conn, stmt);

		} finally {
			watch.commit(produceName);
		}
		return stmt;
	}

	protected CallableStatement execProduce3(String produceName, Object... params) {
		Connection conn = openConn();
		if (conn == null) {
			return null;
		}
		CallableStatement stmt = null;
		try {
			stmt = conn.prepareCall(ProcedureName(produceName, params.length), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			prepareCallable2(stmt, params);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + produceName + "出错" });
			LogLog.error(msg, ex);
			closeConn(conn, stmt);
		} finally {
		}
		return stmt;
	}

	protected int execLastId(String sql, Map<Integer, DbParameter> params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		int result = -1;
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return result;
		}
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql, 1);
			prepareCommand(pstmt, params);
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			if (rs.next())
				result = rs.getInt(1);
		} catch (Exception ex) {
			String msg = "获取自增id出错";
			LogLog.error(msg, ex);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			watch.commit(sql);
		}
		return result;
	}

	protected int execLastId2(String sql, Object... params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		int result = -1;
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return result;
		}
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql, 1);
			prepareCommand2(pstmt, params);
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			if (rs.next())
				result = rs.getInt(1);
		} catch (Exception ex) {
			String msg = "获取自增id出错";
			LogLog.error(msg, ex);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			watch.commit(sql);
		}
		return result;
	}

	protected int execLastId(String sql) {
		return execLastId(sql, null);
	}

	protected int execLastId2(String sql) {
		return execLastId2(sql, null);
	}

	/**
	 * 批处理事务，如大量的新增，删除
	 * 
	 * @param tableName
	 * @param sqlComm
	 * @return
	 */
	protected boolean sqlBatch(String tableName, List<String> sqlComm) {
		if ((sqlComm == null) || (sqlComm.size() == 0)) {
			return true;
		}
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(tableName);
			return false;
		}
		Statement stmt = null;
		try {
			stmt = conn.createStatement(1005, 1008);
			for (String sqlString : sqlComm) {
				stmt.addBatch(sqlString);
			}
			stmt.executeBatch();
			return true;
		} catch (SQLException e) {
			String msg = "批处理执行SQL语句出错";
			LogLog.error(msg, e);
		} finally {
			closeConn(conn, stmt);
			watch.commit(tableName);
		}
		return false;
	}

	protected boolean runProcePrepared(String procName) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		boolean result = false;
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(procName);
			return result;
		}
		String sql = ProcedureName(procName, 0);
		PreparedStatement statement = null;
		try {
			statement = conn.prepareStatement(sql);
			statement.execute();
			result = true;
		} catch (SQLException e) {
			String msg = String.format("执行存储过程%s出错", new Object[] { procName });
			LogLog.error(msg, e);
			result = false;
		} finally {
			closeConn(conn, statement);
			watch.commit(procName);
		}
		return result;
	}

	protected int execCountQuery(String sql, Map<Integer, DbParameter> params) {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		int result = -1;
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return result;
		}
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement(sql);
			prepareCommand(pstmt, params);
			rs = pstmt.executeQuery();
			if ((rs != null) && (rs.next()))
				result = rs.getInt(1);
		} catch (Exception e) {
			LogLog.error("统计Count出错, 调用Sql语句 : " + sql + "出错", e);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			watch.commit(sql);
		}
		return result;
	}

	protected String getDbName() {
		DbWatch watch = ResPool.DbWatch(this.dbMgr.index);
		Connection conn = openConn();
		watch.getPool();
		if (conn == null) {
			watch.commit("get DataBase Name");
			return "";
		}
		String temp = "";
		try {
			temp = conn.getCatalog();
		} catch (SQLException e) {
			LogLog.error("获取数据库名出错", e);
		} finally {
			closeConn(conn);
		}
		return temp;
	}

	protected Connection openConn() {
		return this.dbMgr.openConn();
	}

	// {
	// return DBPoolMgr.getwandianConn();
	// }

	private void closeConn(Connection conn) {
		/*
		 * try { if (conn != null) conn.close(); } catch (SQLException e) {
		 * LogLog.error("关闭数据库出错", e); }
		 */
		//		this.dbMgr.setCurrentDb(DBMgr.TexasPokerDB);
	}

	protected void closeConn(Connection conn, PreparedStatement pstmt) {
		try {
			if (pstmt != null) {
				pstmt.clearParameters();
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			LogLog.error("关闭数据库出错", e);
		}
		closeConn(conn); // 这里为了保证连接池不断开,通知连接池回收
	}

	private void closeConn(Connection conn, Statement pstmt, ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			LogLog.error("关闭数据库出错", e);
		}
		closeConn(conn, pstmt);
	}

	private void closeConn(Connection conn, Statement statement) {
		if (statement != null) {
			try {
				statement.clearBatch();
				statement.close();
			} catch (SQLException e) {
				LogLog.error("关闭数据库出错", e);
			}
		}
		closeConn(conn);
	}

	protected void closeConn(Statement pstmt) {
		try {
			if (pstmt != null) {
				Connection conn = pstmt.getConnection();
				closeConn(conn, pstmt);
			}
		} catch (SQLException e) {
			LogLog.error("关闭db连接时异常", e);
		}
	}

	protected void closeConn(Statement pstmt, ResultSet rs) {
		try {
			if (pstmt != null) {
				Connection conn = pstmt.getConnection();
				closeConn(conn, pstmt, rs);
			}
		} catch (SQLException e) {
			LogLog.error("关闭db连接时异常", e);
		}
	}

	private void prepareCommand(PreparedStatement pstmt, Map<Integer, DbParameter> parms) throws SQLException {
		if ((pstmt == null) || (parms == null))
			return;
		DbParameter dbParameter;
		int key;
		for (Map.Entry<Integer, DbParameter> entry : parms.entrySet()) {
			key = entry.getKey().intValue();
			dbParameter = entry.getValue();
			pstmt.setObject(key, dbParameter.getResult(), dbParameter.getDbtype());
			// pstmt.setObject(key, dbParameter.getResult());
		}
	}

	private void prepareCommand2(PreparedStatement pstmt, Object... parms) throws SQLException {
		if ((pstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= parms.length; i++) {
			pstmt.setObject(i, parms[i - 1]);
		}
	}

	protected void prepareAddBatch(PreparedStatement pstmt, Object... parms) throws SQLException {
		if ((pstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= parms.length; i++) {
			pstmt.setObject(i, parms[i - 1]);
		}
		pstmt.addBatch();
	}

	/**
	 * 使用于开始连续0~n个输出的
	 * 
	 * @param callstmt
	 * @param isOutPut
	 * @param parms
	 * @throws SQLException
	 */
	protected void prepareFreeCallable(CallableStatement callstmt, int isOutPut, Object... parms) throws SQLException {
		if ((callstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= isOutPut; i++) {
			callstmt.registerOutParameter(i, java.sql.Types.INTEGER);
		}
		int totalParams = parms.length;
		if (isOutPut > 0)
			totalParams++;
		for (int i = 1 + isOutPut; i <= totalParams; i++) {
			callstmt.setObject(i, parms[i - 1 - isOutPut]);
		}
	}

	protected void prepareCallable2(CallableStatement callstmt, Object... parms) throws SQLException {
		if ((callstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= parms.length; i++) {
			callstmt.setObject(i, parms[i - 1]);
		}
	}

	private void prepareCallable(CallableStatement callstmt, Map<Integer, DbParameter> parms) throws SQLException {
		if ((callstmt == null) || (parms == null))
			return;
		DbParameter dbParameter;
		int key;
		for (Map.Entry<Integer, DbParameter> entry : parms.entrySet()) {
			key = entry.getKey().intValue();
			dbParameter = entry.getValue();
			if (dbParameter.isInput())
				callstmt.setObject(key, dbParameter.getResult(), dbParameter.getDbtype());
			else {
				callstmt.registerOutParameter(key, dbParameter.getDbtype());
			}
		}

	}

	protected String getPage(String tableName, String fields, int pageSize, int pageNow, String orderString, String paraStr) {
		int beginRow = 0;
		if (pageSize <= 0)
			pageSize = 1;
		if (pageNow <= 0) {
			pageNow = 1;
		}
		beginRow = (pageNow - 1) * pageSize;
		String limitString = " LIMIT " + beginRow + ", " + pageSize;
		String sqlString = "SELECT " + fields + " FROM " + tableName + "  Where " + paraStr + "  Order By " + orderString + limitString + ";";
		return sqlString;
	}

	protected String getPageByBeginRow(String tableName, String fields, int pageSize, int beginRow, String orderString, String paraStr) {
		if (pageSize <= 0)
			pageSize = 1;
		if (beginRow < 0) {
			beginRow = 1;
		}
		String limitString = " LIMIT " + beginRow + ", " + pageSize;
		String sqlString = "SELECT " + fields + " FROM " + tableName + "  Where " + paraStr + "  Order By " + orderString + limitString + ";";
		return sqlString;
	}

	protected String ProcedureName(String procName, int isOutPut, int paraCount) {
		String procedureName = "";
		if (isOutPut > 0) {
			procedureName = ProcedureNameByReturn(procName, paraCount);
		} else {
			procedureName = ProcedureName(procName, paraCount);
		}
		return procedureName;
	}

	protected String ProcedureName(String procName, int paraCount) {
		int paramCount = paraCount;
		String questionMark = "";

		for (int i = 0; i < paramCount; i++) {
			questionMark = questionMark + "?,";
		}
		if (questionMark != "") {
			questionMark = questionMark.substring(0, questionMark.length() - 1);
		}
		return "{call  " + procName + "(" + questionMark + ")}";
	}

	protected String ProcedureNameByReturn(String procName, int paraCount) {
		int paramCount = paraCount;
		String questionMark = "";

		for (int i = 0; i < paramCount; i++) {
			questionMark = questionMark + "?,";
		}
		if (questionMark != "") {
			questionMark = questionMark.substring(0, questionMark.length() - 1);
		}
		return "{?=call  " + procName + "(" + questionMark + ")}";
	}
	// ============通用的查询，插入，删除，更新，分页方法
	// /**
	// * @param v
	// * @return
	// */
	// protected <T> T add(T v) {
	// return v;
	// }
}